#! /bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive


${HIVE_HOME} -S -e "
drop table if exists edu_dws.customer_relationship;
create table edu_dws.customer_relationship(
    ---------------------客户意向表--------------------------

    id                          int                 comment '主键',
    create_date_time            timestamp               ,
    deleted                     int                  comment '是否被删除（禁用）',
    customer_id                 int                  comment '所属客户id',
    origin_type                 string               comment '数据来源',
    itcast_school_id            int                  comment '校区Id',
    itcast_subject_id           int                  comment '学科Id',
    origin_channel              string               comment '来源渠道',
    payment_state               string               comment '支付状态',
    payment_time                string               comment '支付状态变动时间',
    creator                     int                  comment '创建人',
    ---------------------客户线索表----------------------------
    clue_id                          int             comment '线索id',
    customer_relationship_id int                     comment '客户关系id',
    clue_state               string                  comment '线索状态',
    ---------------------客户静态信息表---------------------------
    stu_id                     int                   comment '学员id',
    area                     string                  comment '所在区域',

    ---------------------学科信息表---------------------------
    subject_id                int           comment '学科id',
    subject_name             string         comment '学科名称',

    ---------------------校区表-------------------------------
    school_id                int             comment '校区id',
    school_name             string           comment '校区名称',

    ----------------------员工信息表---------------------------
    employee_id          int             comment '员工id',
    tdepart_id          int              comment '直属部门',

    -------------------- --员工部门表--------------------------
      dep_id             int                 comment '部门id',
    dep_name             string              comment '部门名称'

)
comment '客户意向宽表'
partitioned by (dt string)
row format delimited fields terminated by '\t'
stored as orc tblproperties ('orc.compress'='snappy');


drop table edu_dws.customer_clue;
create table edu_dws.customer_clue(
    -------------------------客户线索表---------------------------
    id                       int ,
    customer_relationship_id int                     comment '客户关系id',
    clue_state               string                  comment '线索状态',

    ---------------------------------------客户意向表字段-----------------------------
    create_date_time            timestamp                ,
    deleted                     int                   comment '是否被删除（禁用）',
    customer_id                 int                   comment '所属客户id',
    origin_type                 string               comment '数据来源',
    itcast_school_id            int                 comment '校区Id',
    itcast_subject_id           int                 comment '学科Id',
    origin_channel              string              comment '来源渠道',
    payment_state               string             comment '支付状态',
    payment_time                string             comment '支付状态变动时间',
    creator                     int                comment '创建人',

    -------------------------------------线索申诉信息表------------------------------------
    customer_relationship_first_id int                comment '第一条客户关系id',
    appeal_status                  int                comment '申诉状态，0:待稽核 1:无效 2：有效'

)
comment '客户线索宽表'
partitioned by (dt string)
row format delimited fields terminated by '\t'
stored as orc
tblproperties ('orc.compress'='snappy');
